﻿using System;
using NHibernate;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using NHibernate.Transform;
using log4net;
using System.Reflection;

namespace DAL.Service.Impl
{
    public class BaseServiceImpl : IBaseService
    {
        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        #region IBaseService 成员 : Object save(Object entity);
        public  object save(object entity)
        {
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    ///session.Load(entity,
                    session.SaveOrUpdate(entity);
                    tx.Commit();
                    return entity;
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    SessionManager.CloseSession();
                }
            }
        }
        #endregion

        #region IBaseService 成员 : void delete(Object entity);
        public void delete(object entity)
        {
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    session.Delete(entity);
                    tx.Commit();
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    session.Close();
                }
            }
        }
        #endregion

        #region IBaseService 成员 : object getEntity(object id, string entityname)
        public object getEntity(object id, string entityname)
        {
            //log.Error("^^^^^^^^^^^^^^^^getEntities Start:" + DateTime.Now.ToString());
            IList lst = new ArrayList();
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    return session.CreateQuery("from " + entityname + " where id=" + id).UniqueResult(); 
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    SessionManager.CloseSession();
                }
            }
        }

        #endregion

        #region IBaseService 成员 : object getEntity(MyQuery query)
        public object getEntity(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    IQuery iquery = session.CreateQuery(query.QueryString);
                    Hashtable paras = query.Paras;
                    foreach (DictionaryEntry de in paras)
                    {
                        iquery.SetParameter((string)de.Key, de.Value);
                    }
                    return iquery.UniqueResult();
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    SessionManager.CloseSession();
                }
            }
        }

        #endregion


        public IList getPageList(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            IDataReader dr;
		    int pageNumber = query.PageSize;// 每页记录数
		    if (pageNumber < 1) {
			    pageNumber = 20;
		    }
            int startPageNo = query.PageNo;// 起如页
		    if (startPageNo < 1) {
			    startPageNo = 1;
		    }
            IList list = new ArrayList();
            cmd.CommandText = query.QueryString;
		    try {
			    int startRecord = pageNumber * (startPageNo - 1) + 1;
			    if (startRecord < 0)
				    startRecord = 0;
			    String start = Convert.ToString((startRecord - 1));
                String end = Convert.ToString(startRecord + pageNumber);
			    query.addParas("@start",start);
                query.addParas("@end", end);
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value));
                }
                //cmd.Prepare();
                dr = cmd.ExecuteReader();
                while (true)
                {
                    if (dr.Read())
                    {
                        int columnCount = dr.FieldCount;
                        Hashtable tb = new Hashtable();
                        for (int i = 0; i < columnCount; i++)
                        {
                            //微站专用 当搜索大于100条时，只显示100条 modify 11/09/23
                            //if (columnCount == 1 && dr.GetName(i) == "total" && (int)dr.GetValue(i) > 100)
                            //{
                            //    tb.Add(dr.GetName(i), 100);
                            //}
                            //else
                            //{
                                tb.Add(dr.GetName(i), dr.GetValue(i));
                            //}
                        }
                        list.Add(tb);
                    }
                    else if (!dr.NextResult())
                        break;
                }                     
                dr.Close();
                log.Info("查询成功!");

		    } catch (Exception ex) {
                log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 181行出错 " + ex));
                throw ex;
			    //System.out.println(ex.toString());
		    } finally {
                try
                {

                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 192行出错 " + sqlex));
                    throw sqlex;
                }

		    }

            return list;

        }
  

        public DataTable getDataTable(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            SqlConnection conn = (SqlConnection)session.Connection;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            //IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = query.QueryString;
            int pageNumber = query.PageSize;// 每页记录数
            if (pageNumber < 1)
            {
                pageNumber = 20;
            }
            int startPageNo = query.PageNo;// 起如页
            if (startPageNo < 1)
            {
                startPageNo = 1;
            }
            int startRecord = pageNumber * (startPageNo - 1) + 1;
            if (startRecord < 0)
                startRecord = 0;
            String start = Convert.ToString((startRecord - 1));
            String end = Convert.ToString(startRecord + pageNumber);
            query.addParas("@start", start);
            query.addParas("@end", end);
            Hashtable paras = query.Paras;
            foreach (DictionaryEntry de in paras)
            {
                cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value == null ? System.DBNull.Value : de.Value));
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            SessionManager.CloseSession();
            return dt;
        }


        #region IBaseService 成员 : void exeUpdateSql(MyQuery query)
        public int exeUpdateSql(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IList list = new ArrayList();
            String sqlString = query.QueryString;
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = query.QueryString;
            IDataReader dr;
            try
            {
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value == null?System.DBNull.Value:de.Value));
                }
                //cmd.Prepare();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 238行出错 " + ex+"  SQL:"+sqlString));
                throw ex;
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 249行出错 " + sqlex + "  SQL:" + sqlString));
                    throw sqlex;
                }

            }
        }
        public int exeUpdateSql(string storedprocedure_name, MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IList list = new ArrayList();
            String sqlString = query.QueryString;
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedprocedure_name;
            try
            {
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value == null ? System.DBNull.Value : de.Value));
                }
                //cmd.Prepare();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    throw sqlex;
                }

            }
        }

        #endregion





        public IList getEntitiesBySql(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IList list = new ArrayList();
            String sqlString = query.QueryString;
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = query.QueryString;
            IDataReader dr;
            try
            {
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value));
                }
                dr = cmd.ExecuteReader();
                int columnCount = dr.FieldCount;
                while (dr.Read())
                {
                    Hashtable tb = new Hashtable();
                    for (int i = 0; i < columnCount; i++)
                    {
                        tb.Add(dr.GetName(i), dr.GetValue(i));
                    }
                    list.Add(tb);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    throw sqlex;
                }

            }
            return list;
        }

        public string getDbVersion(string version)
        {
            if (version == "")
                version = "dbo";
            return version;
        }



        #region IBaseService 成员

        public IList getList(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query.QueryString;
            IDataReader dr;
            int pageNumber = query.PageSize;// 每页记录数
            if (pageNumber < 1)
            {
                pageNumber = 20;
            }
            int startPageNo = query.PageNo;// 起如页
            if (startPageNo < 1)
            {
                startPageNo = 1;
            }
            IList list = new ArrayList();
            try
            {
                int startRecord = pageNumber * (startPageNo - 1) + 1;
                if (startRecord < 0)
                    startRecord = 0;
                String start = Convert.ToString((startRecord - 1));
                String end = Convert.ToString(startRecord + pageNumber);
                query.addParas("@start", start);
                query.addParas("@end", end);

                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value));
                }
                dr = cmd.ExecuteReader();
                while (true)
                {
                    if (dr.Read())
                    {
                        int columnCount = dr.FieldCount;
                        Hashtable tb = new Hashtable();
                        for (int i = 0; i < columnCount; i++)
                        {
                            tb.Add(dr.GetName(i), dr.GetValue(i));
                        }
                        list.Add(tb);
                    }
                    else if (!dr.NextResult())
                        break;
                }
                dr.Close();

            }
            catch (Exception ex)
            {
                log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 347行出错 " + ex));
                throw (ex);
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    log.Error("报错", new Exception("来自DAL工程BaseServiceImpl.cs 358行出错 " + sqlex));
                    throw sqlex;
                }

            }

            return list;
        }

        public object getScalar(MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query.QueryString;
            IList list = new ArrayList();
            try
            {
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value));
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    throw sqlex;
                }
           
            }
            return null; 
        }

        public IList getList(string storedprocedure_name, MyQuery query)
        {
            ISession session = SessionManager.GetCurrentSession();
            IDbConnection conn = session.Connection;
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedprocedure_name;
            IDataReader dr;
            IList list = new ArrayList();
            try
            {
                Hashtable paras = query.Paras;
                foreach (DictionaryEntry de in paras)
                {
                    cmd.Parameters.Add(new SqlParameter((string)de.Key, de.Value));
                }
                dr = cmd.ExecuteReader();
                while (true)
                {
                    if (dr.Read())
                    {
                        int columnCount = dr.FieldCount;
                        Hashtable tb = new Hashtable();
                        for (int i = 0; i < columnCount; i++)
                        {
                            tb.Add(dr.GetName(i), dr.GetValue(i));
                        }
                        list.Add(tb);
                    }
                    else if (!dr.NextResult())
                        break;
                }

            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                try
                {
                    SessionManager.CloseSession();
                }
                catch (Exception sqlex)
                {
                    throw sqlex;
                }

            }

            return list;
        }

        #endregion




        public object getEntity(MyQuery query, string entityName)
        {
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    ISQLQuery iquery = session.CreateSQLQuery(query.QueryString);
                    Hashtable paras = query.Paras;
                    foreach (DictionaryEntry de in paras)
                    {
                        iquery.SetParameter((string)de.Key, de.Value);
                    }
                    return iquery.AddEntity(entityName).UniqueResult();
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    SessionManager.CloseSession();
                }
            }
        }


        public IList getEntities(string ids, string entityname)
        {
            //log.Error("^^^^^^^^^^^^^^^^getEntities Start:" + DateTime.Now.ToString());
            IList lst = new ArrayList();
            ISession session = SessionManager.GetCurrentSession();
            using (ITransaction tx = session.BeginTransaction())
            {
                try
                {
                    return session.CreateQuery("from " + entityname + " where id in ("+ids+")").List();
                }
                catch (HibernateException)
                {
                    tx.Rollback();
                    throw;
                }
                finally
                {
                    SessionManager.CloseSession();
                }
            }
        }





        public IList getList(string tableName)
        {
            if ("areas,industry,menu".IndexOf(tableName) == -1)
                return null;
            MyQuery q = new MyQuery();
            q.QueryString = "select * from " + tableName;
            return this.getList(q);
        }
    }
}
